﻿<%@ WebHandler Language="C#" Class="Reportes" %>

using System;
using System.Web;
using iTextSharp.text;
using iTextSharp.text.pdf;
using System.IO;
using System.Data.SqlClient;

public class Reportes : IHttpHandler {
    
    public void ProcessRequest (HttpContext context) {

        string download = context.Request.QueryString["download"];
        bool isDownload = false;
        if (string.IsNullOrEmpty(download))
        {

        }
        else
        {
            isDownload = download.Equals("true");
        }
        
        string pdfPath = System.Web.HttpContext.Current.Server.MapPath("~/TempPDF/reporte.pdf");
        string formChoices = context.Request.QueryString["formChoices"];
        if (string.IsNullOrEmpty(formChoices))
            formChoices = "000000000";
        if (isDownload)
        {
            context.Response.ContentType = "Application/X-unknown";
            context.Response.AddHeader("Content-Disposition", "attachment; filename=reporte.pdf");
        }
        else
        {

            context.Response.ContentType = "Application/pdf";
            context.Response.AddHeader("content-disposition", "inline; filename=reporte.pdf");
        }
        WritePdf(context, formChoices);
        context.Response.WriteFile(pdfPath);
    }
 
    public bool IsReusable {
        get {
            return false;
        }
    }
    
    private void WritePdf(HttpContext context, string checkBoxes)
    {
        using (Document doc = new Document())
        {
            
            //PdfWriter.GetInstance(doc, new FileStream("~/TempPDF/temp.pdf", FileMode.Create));
            string pdfPath = System.Web.HttpContext.Current.Server.MapPath("~/TempPDF/reporte.pdf");
            PdfWriter.GetInstance(doc, new FileStream(pdfPath, FileMode.Create));
            doc.Open();
            string folio = context.Request.QueryString["folio"];

            doc.AddTitle("Reporte de " + folio);
            doc.Add(new Paragraph(" "));
            doc.Add(new Paragraph("Folio: " + folio));
            doc.Add(new Paragraph("Fecha: " + String.Format("{0:d/M/yyyy} ", DateTime.Now)));
            doc.Add(new Paragraph(" "));
            if (String.IsNullOrEmpty(folio))
            {

            }
            else
            {
                if (checkBoxes[0].Equals('1'))
                {
                    doc.Add(GetDatosSolicitanteTable(folio));
                    doc.Add(new Paragraph(" "));
                }
                if (checkBoxes[1].Equals('1'))
                {
                    doc.Add(GetDatosMotivoTable(folio));
                    doc.Add(new Paragraph(" "));
                }
                if (checkBoxes[2].Equals('1'))
                {
                    doc.Add(GetDatosFamiliaresTable(folio));
                    doc.Add(new Paragraph(" "));
                }
                if (checkBoxes[3].Equals('1'))
                {
                    doc.Add(GetIngresosEgresosTable(folio, "IngresosPersonal"));
                    doc.Add(new Paragraph(" "));
                    doc.Add(GetIngresosEgresosTable(folio, "EgresosPersonal"));
                    doc.Add(new Paragraph(" "));
                }
                if (checkBoxes[4].Equals('1'))
                {
                    doc.Add(GetPropiedadesTable(folio));
                    doc.Add(new Paragraph(" "));
                    doc.Add(GetVehiculosTable(folio));
                    doc.Add(new Paragraph(" "));

                }
                if (checkBoxes[5].Equals('1'))
                {
                    doc.Add(DatosFinancierosActivosTable(folio));
                    doc.Add(new Paragraph(" "));
                    doc.Add(DatosFinancierosPasivosTable(folio));
                    doc.Add(new Paragraph(" "));

                }
                if (checkBoxes[6].Equals('1'))
                {
                    doc.Add(DatosDocumentacionEconomicaTable(folio));
                    doc.Add(new Paragraph(" "));
                }
                if (checkBoxes[7].Equals('1'))
                {
                    doc.Add(DatosComentariosFinales(folio));
                    doc.Add(new Paragraph(" "));
                }
                if (checkBoxes[8].Equals('1'))
                {
                    doc.Add(DatosGastosMensualesTable(folio));
                    doc.Add(new Paragraph(" "));
                    doc.Add(DatosAportadoresTable(folio));
                    doc.Add(new Paragraph(" "));
                }
                /*if (checkBoxes[9].Equals('1'))
                {
                    doc.Add(DatosDictamenTable(folio));
                    doc.Add(new Paragraph(" "));
                }*/
            }
            doc.Close();
        }
    }

    private PdfPTable GetDatosSolicitanteTable(string folio)
    {
        DatabaseConnection dbCon = new DatabaseConnection();

        String query = "SELECT *" +
            " FROM Solicitud, Alumnos " +
            " WHERE Solicitud.Folio =  " + folio +
            " AND Solicitud.matricula = Alumnos.matricula" +
            " ORDER BY Solicitud.Folio desc";
        SqlDataReader formData = dbCon.ExecuteSelect(query);

        PdfPTable table = new PdfPTable(2);
        if (formData.Read())
        {
            PdfPCell headerCell = new PdfPCell(new Phrase("Datos Solicitante"));
            headerCell.Colspan = 2;
            table.AddCell(headerCell);
            table.AddCell("Nombre");
            table.AddCell(formData["Nombre"].ToString() + " " + formData["Apellido"].ToString());
            table.AddCell("Matricula");
            table.AddCell(formData["Matricula"].ToString());
            table.AddCell("Fecha Enviada");
            table.AddCell(formData["Solicitud_Fecha"].ToString());
            PdfPCell cell = new PdfPCell(new Phrase("Numeros de contacto"));
            cell.Rowspan = 2;
            table.AddCell(cell);
            table.AddCell(formData["Datos_Telefono"].ToString());
            table.AddCell(formData["Datos_Celular"].ToString());
            table.AddCell("CorreoPersonal");
            table.AddCell(formData["Datos_CorreoPersonal"].ToString());
            table.AddCell("Domicilio");
            table.AddCell(formData["Datos_DomicilioActual"].ToString());
            table.AddCell("Siguiente Semestre");
            table.AddCell(formData["Datos_SemestreProximo"].ToString());

            table.AddCell("Promedio Actual");
            table.AddCell(formData["Datos_PromAcumulado"].ToString());

            table.AddCell("Promedio de 2 ultimos semestres");
            table.AddCell(formData["Datos_PromSemA1"].ToString() + " " + formData["Datos_PromSemA2"].ToString());


            table.AddCell("Porcentaje Beca");
            table.AddCell(formData["Datos_PorcentajeBeca"].ToString());
            table.AddCell("Porcentaje Credito ");
            table.AddCell(formData["Datos_PorcentajeCredito"].ToString());

        }

        dbCon.CloseConnection();


        return table;
    }

    protected PdfPTable GetDatosMotivoTable(string folio)
    {
        DatabaseConnection dbCon = new DatabaseConnection();

        String query = "SELECT *" +
            " FROM Solicitud " +
            " WHERE Solicitud.Folio =  " + folio +
            " ORDER BY Solicitud.Folio desc";
        SqlDataReader formData = dbCon.ExecuteSelect(query);

        PdfPTable table = new PdfPTable(2);
        if (formData.Read())
        {
            PdfPCell headerCell = new PdfPCell(new Phrase("Motivos Solicitante"));
            headerCell.Colspan = 2;
            table.AddCell(headerCell);
            table.AddCell("Motivo Destino");
            table.AddCell(formData["Motivo_Destino"].ToString());
            table.AddCell("Motivo_AplicarSemestre");
            table.AddCell(formData["Motivo_AplicarSemestre"].ToString());
            table.AddCell("Beca Anterior");
            table.AddCell(formData["Motivo_BecaAnterior"].ToString());
            table.AddCell("Semestres Anteriores");
            table.AddCell(formData["Motivo_SemestresAnteriores"].ToString());

            table.AddCell("Semestres Anteriores");
            table.AddCell(formData["Motivo_SemestresAnteriores"].ToString());


            table.AddCell("Motivo Explicado");
            table.AddCell(formData["Motivo_Motivo"].ToString());




        }
        dbCon.CloseConnection();
        return table;
    }

    protected PdfPTable GetDatosFamiliaresTable(string folio)
    {
        DatabaseConnection dbCon = new DatabaseConnection();

        PdfPTable table = new PdfPTable(3);
        PdfPCell headerCell = new PdfPCell(new Phrase("Personas en Habitacion"));
        headerCell.Colspan = 3;
        table.AddCell(headerCell);

        String query = "SELECT *" +
            " FROM Solicitud " +
            " WHERE Folio =  " + folio +
            " ORDER BY Folio desc";
        SqlDataReader formData = dbCon.ExecuteSelect(query);

        if (formData.Read())
        {
            table.AddCell("Estado civil de los padres");
            PdfPCell cell = new PdfPCell(new Phrase(formData["Datos_EstadoCivilPadres"].ToString()));
            cell.Colspan = 2;
            table.AddCell(cell);
            table.AddCell("Dependencia Economica");
            cell = new PdfPCell(new Phrase(formData["Datos_DependenciaEconomica"].ToString()));
            cell.Colspan = 2;
            table.AddCell(cell);
            table.AddCell("Estado civil de los padres");
            cell = new PdfPCell(new Phrase(formData["Datos_NombreTutor"].ToString()));
            cell.Colspan = 2;
            table.AddCell(cell);
        }
        dbCon.CloseConnection();

        DatabaseConnection dbCon2 = new DatabaseConnection();
        query = "SELECT *" +
            " FROM Familiares " +
            " WHERE Folio =  " + folio +
            " ORDER BY Folio desc";
        formData = dbCon2.ExecuteSelect(query);

        while (formData.Read())
        {//Familiares_Parentesco, Familiares_Edad, Familiares_EstadoCivil

            table.AddCell(formData["Familiares_Parentesco"].ToString());
            table.AddCell(formData["Familiares_Edad"].ToString());
            table.AddCell(formData["Familiares_EstadoCivil"].ToString());

        }

        dbCon2.CloseConnection();
        return table;
    }

    protected PdfPTable GetIngresosEgresosTable(string folio, string tipoTransaccion)
    {
        //IngresosPersonal

        DatabaseConnection dbCon = new DatabaseConnection();

        PdfPTable table = new PdfPTable(2);
        PdfPCell headerCell = new PdfPCell(new Phrase(tipoTransaccion));
        headerCell.Colspan = 2;
        table.AddCell(headerCell);

        String query = "SELECT *" +
            " FROM " + tipoTransaccion +
            " WHERE Folio =  " + folio +
            " ORDER BY Folio desc";
        SqlDataReader formData = dbCon.ExecuteSelect(query);

        double total = 0;

        while (formData.Read())
        {
            table.AddCell(formData[tipoTransaccion + "_Concepto"].ToString());
            string monto = formData[tipoTransaccion + "_Monto"].ToString();
            double montoNum = 0;
            if (double.TryParse(monto, out montoNum))
            {
                total += montoNum;
            }
            table.AddCell(monto);
        }
        table.AddCell("Total de " + tipoTransaccion);
        table.AddCell(total.ToString());
        dbCon.CloseConnection();
        return table;
    }

    protected PdfPTable GetPropiedadesTable(string folio)
    {
        DatabaseConnection dbCon = new DatabaseConnection();

        PdfPTable table = new PdfPTable(8);
        PdfPCell headerCell = new PdfPCell(new Phrase("Propiedades"));
        headerCell.Colspan = 8;
        table.AddCell(headerCell);

        String query = "SELECT *" +
            " FROM Propiedades" +
            " WHERE Folio =  " + folio +
            " ORDER BY Folio desc";
        SqlDataReader formData = dbCon.ExecuteSelect(query);
        int index = 1;
        table.AddCell("Indice");
        table.AddCell("FechaAdquisicion");
        table.AddCell("Descripcion");
        table.AddCell("SuperficialConstruida");
        table.AddCell("ValorComercial");
        table.AddCell("CantidadAdeudo");
        table.AddCell("Plazo");
        table.AddCell("PagoMensualRenta");

        while (formData.Read())
        {
            table.AddCell(index.ToString());
            index++;
            table.AddCell(formData["Propiedades_FechaAdquisicion"].ToString());
            table.AddCell(formData["Propiedades_Descripcion"].ToString());
            table.AddCell(formData["Propiedades_SuperficialConstruida"].ToString());
            table.AddCell(formData["Propiedades_ValorComercial"].ToString());
            table.AddCell(formData["Propiedades_CantidadAdeudo"].ToString());
            table.AddCell(formData["Propiedades_Plazo"].ToString());
            table.AddCell(formData["Propiedades_PagoMensualRenta"].ToString());
        }
        dbCon.CloseConnection();
        return table;
    }

    protected PdfPTable GetVehiculosTable(string folio) //TODO: load vehicles here!
    {
        DatabaseConnection dbCon = new DatabaseConnection();

        PdfPTable table = new PdfPTable(8);
        PdfPCell headerCell = new PdfPCell(new Phrase("Vehiculos"));
        headerCell.Colspan = 8;
        table.AddCell(headerCell);

        String query = "SELECT *" +
            " FROM Vehiculos" +
            " WHERE Folio =  " + folio +
            " ORDER BY Folio desc";
        SqlDataReader formData = dbCon.ExecuteSelect(query);
        int index = 1;
        table.AddCell("Indice");
        table.AddCell("Propietario");
        table.AddCell("Marca");
        table.AddCell("Modelo");
        table.AddCell("Valor");
        table.AddCell("Canitidad Adeudo");
        table.AddCell("Plazo");
        table.AddCell("Pago Mensual");

        while (formData.Read())
        {
            table.AddCell(index.ToString());
            index++;
            table.AddCell(formData["Vehiculo_Propietario"].ToString());
            table.AddCell(formData["Vehiculo_Marca"].ToString());
            table.AddCell(formData["Vehiculo_Modelo"].ToString());
            table.AddCell(formData["Vehiculo_ValorComercial"].ToString());
            table.AddCell(formData["Vehiculo_CantidadAdeudo"].ToString());
            table.AddCell(formData["Vehiculo_Plazo"].ToString());
            table.AddCell(formData["Vehiculo_PagoMensual"].ToString());
        }
        dbCon.CloseConnection();
        return table;
    }

    protected PdfPTable DatosFinancierosActivosTable(string folio)
    {
        DatabaseConnection dbCon = new DatabaseConnection();

        PdfPTable table = new PdfPTable(6);
        PdfPCell headerCell = new PdfPCell(new Phrase("Financieros Activos"));
        headerCell.Colspan = 7;
        table.AddCell(headerCell);

        String query = "SELECT *" +
            " FROM Activos" +
            " WHERE Folio =  " + folio +
            " ORDER BY Folio desc";
        SqlDataReader formData = dbCon.ExecuteSelect(query);
        int index = 1;
        table.AddCell("Indice");
        table.AddCell("Institucion");
        table.AddCell("Descripcion");
        table.AddCell("Monto Actual");
        table.AddCell("Numero de Cuenta");
        table.AddCell("IngresoAnual");
        while (formData.Read())
        {
            table.AddCell(index.ToString());
            index++;
            table.AddCell(formData["Activos_Institucion"].ToString());
            table.AddCell(formData["Activos_Descripcion"].ToString());
            table.AddCell(formData["Activos_MontoActual"].ToString());
            table.AddCell(formData["Activos_NumeroCuenta"].ToString());
            table.AddCell(formData["Activos_IngresoAnual"].ToString());
        }
        dbCon.CloseConnection();
        return table;
    }

    protected PdfPTable DatosFinancierosPasivosTable(string folio)
    {
        DatabaseConnection dbCon = new DatabaseConnection();

        PdfPTable table = new PdfPTable(7);
        PdfPCell headerCell = new PdfPCell(new Phrase("Financieros Pasivos"));
        headerCell.Colspan = 7;
        table.AddCell(headerCell);

        String query = "SELECT *" +
            " FROM Pasivos" +
            " WHERE Folio =  " + folio +
            " ORDER BY Folio desc";
        SqlDataReader formData = dbCon.ExecuteSelect(query);
        int index = 1;
        table.AddCell("Indice");
        table.AddCell("Institucion");
        table.AddCell("Numero de Cuenta");
        table.AddCell("Monto Actual");
        table.AddCell("Monto Adeudado");
        table.AddCell("Plazo");
        table.AddCell("Pago Mensual");
        while (formData.Read())
        {
            table.AddCell(index.ToString());
            index++;
            table.AddCell(formData["Pasivos_Institucion"].ToString());
            table.AddCell(formData["Pasivos_NumeroCuenta"].ToString());
            table.AddCell(formData["Pasivos_MontoActual"].ToString());
            table.AddCell(formData["Pasivos_MontoAdeudado"].ToString());
            table.AddCell(formData["Pasivos_Plazo"].ToString());
            table.AddCell(formData["Pasivos_PagoMensual"].ToString());
        }
        dbCon.CloseConnection();
        return table;
    }

    protected PdfPTable DatosDocumentacionEconomicaTable(string folio)
    {
        DatabaseConnection dbCon = new DatabaseConnection();

        PdfPTable table = new PdfPTable(1);
        PdfPCell headerCell = new PdfPCell(new Phrase("Documentacion Economica"));
        headerCell.Colspan = 1;
        table.AddCell(headerCell);

        String query = "SELECT *" +
            " FROM DocumentacionSituacionEconomica" +
            " WHERE Folio =  " + folio +
            " AND DocumentacionSituacionEconomica_Check = 'True'" +
            " ORDER BY Folio desc";
        SqlDataReader formData = dbCon.ExecuteSelect(query);
        while (formData.Read())
        {//[DocumentacionSituacionEconomica_Concepto], [DocumentacionSituacionEconomica_Check], [DocumentacionSituacionEconomica_Tipo]
            table.AddCell("-" + formData["DocumentacionSituacionEconomica_Concepto"].ToString());

        }
        dbCon.CloseConnection();
        return table;
    }

    protected PdfPTable DatosComentariosFinales(string folio)
    {
        DatabaseConnection dbCon = new DatabaseConnection();
        int numColomns = 1;
        PdfPTable table = new PdfPTable(numColomns);
        PdfPCell headerCell = new PdfPCell(new Phrase("Comentarios Finales"));
        headerCell.Colspan = numColomns;
        table.AddCell(headerCell);

        String query = "SELECT Comentarios_Comite" +
          " FROM solicitud" +
          " WHERE Folio =  " + folio +
          " ORDER BY Folio desc";
        SqlDataReader formData = dbCon.ExecuteSelect(query);
        if (formData.Read())
        {
            table.AddCell(formData["Comentarios_Comite"].ToString());
        }
        dbCon.CloseConnection();
        return table;
    }

    protected PdfPTable DatosGastosMensualesTable(string folio)
    {
        DatabaseConnection dbCon = new DatabaseConnection();
        int numColomns = 2;
        PdfPTable table = new PdfPTable(numColomns);
        PdfPCell headerCell = new PdfPCell(new Phrase("Desembolso Familiar"));
        headerCell.Colspan = numColomns;
        table.AddCell(headerCell);

        String query = "SELECT *" +
          " FROM DesembolsoFamiliar" +
          " WHERE Folio =  " + folio +
          " ORDER BY Folio desc";
        SqlDataReader formData = dbCon.ExecuteSelect(query);
        double total = 0;
        while (formData.Read())
        {
            table.AddCell(formData["DesembolsoFamiliar_Concepto"].ToString());
            string monto = formData["DesembolsoFamiliar_Monto"].ToString();
            total += double.Parse(monto);
            table.AddCell(monto);

        }
        table.AddCell("Total:");
        table.AddCell(total.ToString());
        dbCon.CloseConnection();
        return table;
    }

    protected PdfPTable DatosAportadoresTable(string folio)
    {
        DatabaseConnection dbCon = new DatabaseConnection();
        int numColomns = 6;
        PdfPTable table = new PdfPTable(numColomns);
        PdfPCell headerCell = new PdfPCell(new Phrase("Documentacion Economica"));
        headerCell.Colspan = numColomns;
        table.AddCell(headerCell);

        String query = "SELECT *" +
            " FROM Aportadores" +
            " WHERE Folio =  " + folio +
            " ORDER BY Folio desc";
        SqlDataReader formData = dbCon.ExecuteSelect(query);
        int index = 1;
        while (formData.Read())
        {/*
          * ,[Aportadores_Principal] ,[Aportadores_NombreCompleto] ,[Aportadores_NombreEmpresa] ,[Aportadores_Puesto] " +
                                   ",[Aportadores_Parentesco] ,[Aportadores_Sueldo] ,[Aportadores_InteresesDividendos] ,[Aportadores_Honorarios] " +
                                   ",[Aportadores_Rentas] ,[Aportadores_VentasComisiones] ,[Aportadores_Aguinaldo] ,[Aportadores_Ahorro] " +
                                   ",[Aportadores_Bonos] ,[Aportadores_Otros])
          * */
            string principal = "";
            string test = formData["Aportadores_Principal"].ToString();
            if (formData["Aportadores_Principal"].ToString().Equals("True"))
                principal = "[Aportador Principal]";
            PdfPCell cell = new PdfPCell(new Phrase(index + ". " + formData["Aportadores_NombreCompleto"] + " " + principal));
            cell.Colspan = numColomns;
            table.AddCell(cell);
            table.AddCell("Empresa:");
            table.AddCell(formData["Aportadores_NombreEmpresa"].ToString());
            table.AddCell("Puesto:");
            table.AddCell(formData["Aportadores_Puesto"].ToString());
            table.AddCell("Parentesco:");
            table.AddCell(formData["Aportadores_Parentesco"].ToString());
            table.AddCell("Sueldo:");
            table.AddCell(double.Parse(formData["Aportadores_Sueldo"].ToString()).ToString());
            table.AddCell("Rentas:");
            table.AddCell(double.Parse(formData["Aportadores_Rentas"].ToString()).ToString());
            table.AddCell("Ahorro:");
            table.AddCell(double.Parse(formData["Aportadores_Ahorro"].ToString()).ToString());
            table.AddCell("Intereses Dividendos:");
            table.AddCell(double.Parse(formData["Aportadores_InteresesDividendos"].ToString()).ToString());
            table.AddCell("Ventas:");
            table.AddCell(double.Parse(formData["Aportadores_VentasComisiones"].ToString()).ToString());
            table.AddCell("Bonos:");
            table.AddCell(double.Parse(formData["Aportadores_Bonos"].ToString()).ToString());
            table.AddCell("Honorarios:");
            table.AddCell(double.Parse(formData["Aportadores_Honorarios"].ToString()).ToString());
            table.AddCell("Aquinaldo:");
            table.AddCell(double.Parse(formData["Aportadores_Aguinaldo"].ToString()).ToString());
            table.AddCell("Otros:");
            table.AddCell(double.Parse(formData["Aportadores_Otros"].ToString()).ToString());
            index++;
        }
        dbCon.CloseConnection();
        return table;
    }

    protected PdfPTable DatosDictamenTable(string folio)
    {
        DatabaseConnection dbCon = new DatabaseConnection();
        int numColomns = 7;
        PdfPTable table = new PdfPTable(numColomns);
        PdfPCell headerCell = new PdfPCell(new Phrase("Miembro de Familia e Ingresos"));
        headerCell.Colspan = numColomns;
        table.AddCell(headerCell);

        String query = "SELECT Comentarios_Comite" +
          " FROM solicitud" +
          " WHERE Folio =  " + folio +
          " ORDER BY Folio desc";
        SqlDataReader formData = dbCon.ExecuteSelect(query);
        if (formData.Read())
        {
            table.AddCell(formData["Comentarios_Comite"].ToString());
        }
        dbCon.CloseConnection();
        return table;
    }

}